package com.bonc.rms.jpa.tms;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.bonc.rms.entity.tms.TalentEntity;

public interface TalentRepository extends JpaRepository<TalentEntity, Integer> {

	public String findById(int id);

	@Query("SELECT COUNT(*)  FROM TalentEntity WHERE TIMESTAMPDIFF (YEAR,entryDate,?1)>=?2 AND TIMESTAMPDIFF (YEAR,entryDate,?1)<?3")
	public int getAllWorkAgeByDate(String sdf, int startYear, int endYear);

	@Query("SELECT COUNT(*) FROM TalentEntity WHERE TIMESTAMPDIFF (YEAR,entryDate,?1)>=?2 AND TIMESTAMPDIFF (YEAR,entryDate,?1)<?3 AND departmentId=?4")
	public int getAllWorkAgeByDateGroupByDepartmentId(String sdf,
			int startYear, int endYear, Integer departmentId);
	//SELECT COUNT(*) FROM talent WHERE SUBSTRING(identity_card_number,9,1)=8z
	@Query(nativeQuery=true, value="SELECT COUNT(*) FROM talent WHERE SUBSTRING(identity_card_number,9,1)=?1")
	public int getAllAge(int year);

	@Query(nativeQuery=true, value="SELECT COUNT(*) FROM talent WHERE SUBSTRING(identity_card_number,9,1)=?1 AND department_id=?2")
	//@Query("SELECT COUNT(*) FROM TalentEntity WHERE substring(cast(identityCardNumber as string),9,1)=?1 AND departmentId=?2")
	public int getAllAgeById(int i, Integer departmentId);
	
	@Query(nativeQuery=true, value="SELECT count(id) AS SUM, date_format(t.entry_date, '%Y%m') AS MONTH FROM talent t WHERE t.entry_date >= ?1 AND t.entry_date <= ?2 GROUP BY date_format(t.entry_date, '%Y%m') ORDER BY MONTH")
	public List<Map<String, String>> getEntryRate(Date startDate, Date endDate);

	@Query(nativeQuery = true,value ="SELECT COUNT(*) FROM talent WHERE YEAR(entry_date)=?1 AND MONTH(entry_date)=?2")
	public int getAndEmploye(String yearSt, int sum1);

	@Query(nativeQuery = true,value ="SELECT COUNT(*) FROM talent WHERE YEAR(entry_date)<?1 OR (YEAR(entry_date)=?1 AND MONTH(entry_date)=?2)")
	public int getLastYearEmploye(int lastYear, int sum1);
}
